def create_add_template(schema_name, table_name, id_field, field_list):
    insert_sql = """insert into %s.%s""" % (schema_name, table_name)
    field_sql = " %s," % id_field
    value_sql = " #{%s}," % id_field
    for field in field_list:
        field_name = field["field_name"].lower()
        if field_name == id_field:
            continue

        field_sql += """\n <if test="%s != null"> %s,</if>""" % (field_name, field_name)
        value_sql += """\n <if test="%s != null"> #{%s},</if>""" % (field_name, field_name)

    insert_sql += """(%s)\n values \n(%s)""" % (field_sql.rstrip(","), value_sql.rstrip(","))
    return insert_sql


def create_del_template(schema_name, table_name, id_field):
    delete_sql = """delete from %s.%s where %s=#{%s}""" % (schema_name, table_name, id_field, id_field)
    return delete_sql


def create_edit_template(schema_name, table_name, id_field, field_list):
    update_sql = """update  %s.%s """ % (schema_name, table_name)
    set_sql = ""
    where_sql = " %s = #{%s}" % (id_field, id_field)
    for tableField in field_list:
        field_name = tableField["field_name"].lower()
        if field_name == id_field:
            continue

        set_sql += """\n<if test="%s != null"> %s=#{%s},</if>""" % (field_name, field_name, field_name)
    if len(set_sql) > 0:
        set_sql = "\n<set>" + set_sql + "\n</set>"
        update_sql += """ %s \n where %s""" % (set_sql.rstrip(","), where_sql)
    return update_sql


def create_row_template(schema_name, table_name, id_field):
    select_sql = """select * from %s.%s where %s=#{%s} """ % (schema_name, table_name, id_field, id_field)
    return select_sql


def create_list_template(schema_name, table_name, like_key, field_list):
    like_key_list = []
    if like_key_list:
        like_key_list = like_key.split(",")
    select_sql = """select * \n from %s.%s """ % (schema_name, table_name)
    where_sql = ""
    for field in field_list:
        field_name = field["field_name"].lower()
        if field_name in like_key_list:
            where_sql += """\n<if test="%s != null"> %s like #{%s} and</if>""" % (field_name, field_name, field_name)
        else:
            where_sql += """\n<if test="%s != null"> %s=#{%s} and</if>""" % (field_name, field_name, field_name)
    where_sql += "\n 1=1 and"
    select_sql += """ \n where %s""" % where_sql.rstrip("and")
    return select_sql


def create_get_template():
    get_sql = """select \nt1.*,t2.* \nfrom table_name t1 \nleft join table_name_2 t2 on t2.id=t1.xxx  \nwhere t1.field_name=#{param_name}"""
    return get_sql


def create_select_template():
    select_sql = """select \nid as value,field_name as label,field_name_2,field_name_3 \nfrom table_name  \nwhere field_name like #{param_name}"""
    return select_sql


def create_tree_template():
    tree_sql = """select \nid as value,organ_father as parentId,organ_name as label,symbol12 as weight \nfrom oadat.organ  \nwhere field_name = #{param_name}"""
    return tree_sql
